package com.css.zfzx.sjcj.modules.qhrisklevelmappolygon.repository;

import com.css.bpm.platform.api.local.PlatformAPI;
import com.css.bpm.platform.org.role.repository.entity.RoleEntity;
import com.css.bpm.platform.utils.PlatformDateUtils;
import com.css.bpm.platform.utils.PlatformObjectUtils;
import com.css.bpm.platform.utils.PlatformSessionUtils;
import com.css.bpm.platform.utils.UUIDGenerator;
import com.css.zfzx.sjcj.common.utils.ProjectTransUtil;
import com.css.zfzx.sjcj.common.utils.ServerUtil;
import com.css.zfzx.sjcj.common.utils.VectorDataUtil;
import com.css.zfzx.sjcj.modules.boundary.repository.entity.BoundaryEntity;
import com.css.zfzx.sjcj.modules.boundary.viewobjects.BoundaryQueryParams;
import com.css.zfzx.sjcj.modules.qhrisklevelmappolygon.repository.entity.QhRiskLevelMapPolygonEntity;
import com.css.zfzx.sjcj.modules.qhrisklevelmappolygon.viewobjects.QhRiskLevelMapPolygonQueryParams;
import org.gdal.gdal.gdal;
import org.gdal.ogr.*;
import org.gdal.ogr.Driver;
import org.gdal.osr.SpatialReference;
import org.geotools.data.simple.SimpleFeatureCollection;
import org.geotools.data.simple.SimpleFeatureIterator;
import org.opengis.feature.simple.SimpleFeature;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.PropertySource;
import org.springframework.stereotype.Repository;

import java.sql.*;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.List;
import java.util.Date;

/**
 * 初始化数据
 * 解决上万条数据插入慢的问题
 *
 * @Author leon
 * @Date: 2021/2/2 10:24 上午
 */
@Repository
@PropertySource("application-dev.yml")
public class QhRiskLevelMapPolygonImport {
    /**
     * 数据源
     * 10.19.41.111
     */
    @Value("${spring.datasource.url}")
    private String url = "";
    @Value("${spring.datasource.username}")
    private String name = "";
    @Value("${spring.datasource.password}")
    private String pwd = "";

    /**
     * 获取数据库连接
     * SQL语句
     */
    private Connection getConnection() throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection conn = DriverManager.getConnection(url, name, pwd);
        return conn;
    }


    /**
     * sql 批量插入
     *
     * @return
     */
    public int sqlBatchSaveEntities(List<QhRiskLevelMapPolygonEntity> list) throws Exception {
        int count = 0;

        Connection conn = getConnection();

        List<QhRiskLevelMapPolygonEntity> subList = null;
        int total = list.size(), num = 5000, time = total / num + 1, from = 0, to = 0;
        for (int i = 0; i < time; i++) {
            from = i * num;
            to = (i == time - 1) ? total : (i + 1) * num;
            subList = list.subList(from, to);
            if(subList!=null && subList.size()>0) {
                StringBuffer sql = new StringBuffer("insert into qh_risk_level_map_polygon (uuid, province, city, area, affiliated_file_name, coordinate, level, " +
                        "extend1, extend2, extend3, extend4, extend5, extend6, extend7, extend8, extend9, extend10, " +
                        "object_code, partition_flag, create_user, create_time ) "
                        + "values ");
                // 上万条数据
                for (QhRiskLevelMapPolygonEntity entity : subList) {
                    count++;
                    String id = UUIDGenerator.getUUID();
                    String province = entity.getProvince();
                    String city = entity.getCity();
                    String area = entity.getArea();
                    String affiliatedFileName = entity.getAffiliatedFileName();
                    String coordinate = entity.getCoordinate();
                    String level = entity.getLevel();
                    String extend1 = entity.getExtend1();
                    extend1 = (extend1 != null ? extend1 : "");
                    String extend2 = entity.getExtend2();
                    extend2 = (extend2 != null ? extend2 : "");
                    String extend3 = entity.getExtend3();
                    extend3 = (extend3 != null ? extend3 : "");
                    String extend4 = entity.getExtend4();
                    extend4 = (extend4 != null ? extend4 : "");
                    String extend5 = entity.getExtend5();
                    extend5 = (extend5 != null ? extend5 : "");
                    String extend6 = entity.getExtend6();
                    extend6 = (extend6 != null ? extend6 : "");
                    String extend7 = entity.getExtend7();
                    extend7 = (extend7 != null ? extend7 : "");
                    String extend8 = entity.getExtend8();
                    extend8 = (extend8 != null ? extend8 : "");
                    String extend9 = entity.getExtend9();
                    extend9 = (extend9 != null ? extend9 : "");
                    String extend10 = entity.getExtend10();
                    extend10 = (extend10 != null ? extend10 : "");

                    String objectCode = entity.getObjectCode();
                    objectCode = (objectCode != null ? objectCode : "");
                    String partitionFlag = entity.getPartitionFlag();
                    partitionFlag = (partitionFlag != null ? partitionFlag : "");
                    String createUser = PlatformSessionUtils.getUserId();
                    createUser = (createUser != null ? createUser : "");
                    Timestamp createTime = PlatformDateUtils.getCurrentTimestamp();
                    /*String updateUser = PlatformSessionUtils.getUserId();
                    updateUser = (updateUser != null ? updateUser : "");
                    Timestamp updateTime = PlatformDateUtils.getCurrentTimestamp();*/

                    sql.append("('" + id + "','" + province + "','" + city + "','" + area + "','" + affiliatedFileName + "','" + coordinate + "','" + level +
                            "','" + extend1 + "','" + extend2 + "','" + extend3 + "','" + extend4 + "','" + extend5 + "','" + extend6 + "','" + extend7 + "','" + extend8 + "','" + extend9 + "','" + extend10
                            + "','" + objectCode + "','" + partitionFlag + "','" + createUser + "','" + createTime + "'),");
                }
                String sql_str = sql.toString();
                sql_str = sql_str.substring(0, sql_str.length() - 1);
                PreparedStatement ps = conn.prepareStatement(sql_str);
                ps.executeUpdate(sql_str);
                if (ps != null) ps.close();
            }
        }
        if (conn != null) conn.close();
        return count;
    }

    public int sqlBatchDeleteEntities(List<QhRiskLevelMapPolygonEntity> list) {
        int count = 0;
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            /**
             * 获取数据库连接
             */
            conn = getConnection();

            StringBuffer sql = new StringBuffer("DELETE FROM qh_risk_level_map_polygon WHERE uuid IN ( ");
            // 上万条数据
            for (QhRiskLevelMapPolygonEntity entity : list) {
                count++;
                String id = entity.getUuid();
                sql.append("'" + id + "',");
            }
            String sql_str = sql.toString().substring(0, sql.length() - 1) + " )";
            ps = conn.prepareStatement(sql_str);
            ps.executeUpdate(sql_str);
        } catch (Exception e) {
            e.printStackTrace();
            System.err.println("删除第" + count + "条数据出现异常!");
        } finally {
            try {
                if (ps != null) {
                    // 关闭连接
                    ps.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (Exception e2) {
                e2.printStackTrace();
            }
        }
        return count;
    }

    public int sqlBatchLogicDeleteEntities(List<QhRiskLevelMapPolygonEntity> list) {
        int count = 0;
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            /**
             * 获取数据库连接
             */
            conn = getConnection();

            StringBuffer sql = new StringBuffer("UPDATE qh_risk_level_map_polygon set is_valid = '0',update_user='" + PlatformSessionUtils.getUserId() + "',update_time='" + PlatformDateUtils.getCurrentTimestamp() + "' WHERE uuid IN ( ");
            // 上万条数据
            for (QhRiskLevelMapPolygonEntity entity : list) {
                count++;
                String id = entity.getUuid();
                sql.append("'" + id + "',");
            }
            String sql_str = sql.toString().substring(0, sql.length() - 1) + " )";
            ps = conn.prepareStatement(sql_str);
            ps.executeUpdate(sql_str);
        } catch (Exception e) {
            e.printStackTrace();
            System.err.println("删除第" + count + "条数据出现异常!");
        } finally {
            try {
                if (ps != null) {
                    // 关闭连接
                    ps.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (Exception e2) {
                e2.printStackTrace();
            }
        }
        return count;
    }


    public List<QhRiskLevelMapPolygonEntity> searchRiskLevelMapPolygons(QhRiskLevelMapPolygonQueryParams queryParams, String superCode, String provinceCode, String cityCode, String areaCode) {

        List<QhRiskLevelMapPolygonEntity> result = new ArrayList<>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = getConnection();

            StringBuffer sql = new StringBuffer("select affiliated_file_name, coordinate, level from qh_risk_level_map_polygon ");

            StringBuilder whereSql = new StringBuilder(" where 1=1 ");
            if(!PlatformObjectUtils.isEmpty(queryParams.getProvince())) {
                whereSql.append(" and province like '%" + queryParams.getProvince() + "%'");
            }
            if(!PlatformObjectUtils.isEmpty(queryParams.getCity())) {
                whereSql.append(" and city like '%" + queryParams.getCity() + "%'");
            }
            if(!PlatformObjectUtils.isEmpty(queryParams.getArea())) {
                whereSql.append(" and area like '%" + queryParams.getArea() + "%'");
            } else {
                whereSql.append(" and (area IS NULL OR LENGTH(area)=0) ");
            }
            whereSql.append(" and is_valid = '1' ");

            /*if (!PlatformObjectUtils.isEmpty(queryParams.getBounds())) {
                String coordinate = queryParams.getBounds();
                String[] coords = coordinate.split(",");
                String[] coord0 = coords[0].split(" ");
                String[] coord1 = coords[1].split(" ");
                whereSql.append(" and MBRContains(ST_GeometryFromText('POLYGON ((" + coords[0] + "," + (coord0[0] + " " + coord1[1]) + "," + coords[1] + "," + (coord1[0] + " " + coord0[1]) + "," + coords[0] + "))'), ST_GeometryFromText(CONCAT('POINT (', A, ' ', B, ')')))");
            }*/

            String userId = PlatformSessionUtils.getUserId(), isHaveAuthorityControl = queryParams.getIsHaveAuthorityControl();;
            List<RoleEntity> roles = PlatformAPI.getOrgAPI().getUserAPI().getRoles(userId);
            // 是否为超级管理员
            boolean containtRole = ServerUtil.isContaintRole(roles, superCode);
            // 是否为省级管理员
            boolean containtRolePro = ServerUtil.isContaintRole(roles, provinceCode);
            // 是否为市级管理员
            boolean containtRoleCity = ServerUtil.isContaintRole(roles, cityCode);
            // 是否为区级管理员
            boolean containtRoleArea = ServerUtil.isContaintRole(roles, areaCode);

            if ("1".equals(isHaveAuthorityControl) && !containtRole) {
                // 如果都不是只能查看个人表单
                if (!containtRolePro && !containtRoleCity && !containtRoleArea) {
                    whereSql.append(" and create_user ='" + userId + "'");
                }
                String divisionName = ServerUtil.getDivisionEntity().getDivisionName();
                // 省级管理员
                if (containtRolePro) {
                    whereSql.append(" and province like '%" + divisionName + "%'");
                }
                // 市级管理员
                if (containtRoleCity) {
                    whereSql.append(" and city like '%" + divisionName + "%'");
                }
                // 区级管理员
                if (containtRoleArea) {
                    whereSql.append(" and area like '%" + divisionName + "%'");
                }
            }

            String sql_str = sql.append(whereSql.toString()).toString();
            ps = conn.prepareStatement(sql_str);
            rs = ps.executeQuery();
            while (rs.next()) {
                QhRiskLevelMapPolygonEntity entity = new QhRiskLevelMapPolygonEntity();
                String fileName = rs.getString("affiliated_file_name");
                entity.setAffiliatedFileName(fileName);
                String coordinate = rs.getString("coordinate");
                entity.setCoordinate(coordinate);
                String level = rs.getString("level");
                entity.setLevel(level);
                result.add(entity);
            }
        } catch (Exception e2) {
            e2.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (Exception e2) {
                e2.printStackTrace();
            }
            try {
                if (ps != null) {
                    // 关闭连接
                    ps.close();
                }
            } catch (Exception e2) {
                e2.printStackTrace();
            }
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (Exception e2) {
                e2.printStackTrace();
            }
        }

        return result;
    }

    public List<QhRiskLevelMapPolygonEntity> queryQhRiskLevelMapPolygonsByFileName(QhRiskLevelMapPolygonQueryParams queryParams, String sort, String order,
                                                                                                   String superCode, String provinceCode, String cityCode, String areaCode) {
        List<QhRiskLevelMapPolygonEntity> result = new ArrayList<>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = getConnection();

            StringBuilder sql = new StringBuilder("select affiliated_file_name, create_time from qh_risk_level_map_polygon ");
            StringBuilder whereSql = new StringBuilder(" where 1=1 ");
            if(!PlatformObjectUtils.isEmpty(queryParams.getProvince())) {
                whereSql.append(" and province like '%" + queryParams.getProvince() + "%'");
            }
            if(!PlatformObjectUtils.isEmpty(queryParams.getCity())) {
                whereSql.append(" and city like '%" + queryParams.getCity() + "%'");
            }
            if(!PlatformObjectUtils.isEmpty(queryParams.getArea())) {
                whereSql.append(" and area like '%" + queryParams.getArea() + "%'");
            } else {
                whereSql.append(" and (area IS NULL OR LENGTH(area)=0) ");
            }
            whereSql.append(" and is_valid = '1' ");

            String userId = PlatformSessionUtils.getUserId(), isHaveAuthorityControl = queryParams.getIsHaveAuthorityControl();;
            List<RoleEntity> roles = PlatformAPI.getOrgAPI().getUserAPI().getRoles(userId);
            // 是否为超级管理员
            boolean containtRole = ServerUtil.isContaintRole(roles, superCode);
            // 是否为省级管理员
            boolean containtRolePro = ServerUtil.isContaintRole(roles, provinceCode);
            // 是否为市级管理员
            boolean containtRoleCity = ServerUtil.isContaintRole(roles, cityCode);
            // 是否为区级管理员
            boolean containtRoleArea = ServerUtil.isContaintRole(roles, areaCode);

            if ("1".equals(isHaveAuthorityControl) && !containtRole) {
                // 如果都不是只能查看个人表单
                if (!containtRolePro && !containtRoleCity && !containtRoleArea) {
                    whereSql.append(" and create_user ='" + userId + "'");
                }
                String divisionName = ServerUtil.getDivisionEntity().getDivisionName();
                // 省级管理员
                if (containtRolePro) {
                    whereSql.append(" and province like '%" + divisionName + "%'");
                }
                // 市级管理员
                if (containtRoleCity) {
                    whereSql.append(" and city like '%" + divisionName + "%'");
                }
                // 区级管理员
                if (containtRoleArea) {
                    whereSql.append(" and area like '%" + divisionName + "%'");
                }
            }
            //test--zyt限制查询1条记录
            whereSql.append(" limit 1 ");

            String sql_str = sql.append(whereSql.toString()).toString();
            ps = conn.prepareStatement(sql_str);
            rs = ps.executeQuery();
            while (rs.next()) {
                QhRiskLevelMapPolygonEntity entity = new QhRiskLevelMapPolygonEntity();
                String name = rs.getString("affiliated_file_name");
                entity.setAffiliatedFileName(name);
                Date create_time = rs.getDate("create_time");
                entity.setCreateTime(create_time);
                result.add(entity);
            }
            if ("create_time".equals(sort)) {
                Collections.sort(result, new Comparator<QhRiskLevelMapPolygonEntity>() {
                    @Override
                    public int compare(QhRiskLevelMapPolygonEntity o1, QhRiskLevelMapPolygonEntity o2) {
                        if ("asc".equals(order)) {
                            return o1.getCreateTime().compareTo(o2.getCreateTime());
                        } else {
                            return o2.getCreateTime().compareTo(o1.getCreateTime());
                        }
                    }
                });
            }
        } catch (Exception e2) {
            e2.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (Exception e2) {
                e2.printStackTrace();
            }
            try {
                if (ps != null) {
                    // 关闭连接
                    ps.close();
                }
            } catch (Exception e2) {
                e2.printStackTrace();
            }
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (Exception e2) {
                e2.printStackTrace();
            }
        }

        return result;
    }

    //保存--zyt
    public int newSqlBatchSaveEntities(String filePath, String province, String city, String area, String codeType, int dataLength, String createUser) {
        int count = 0;
        Connection conn = null;
        try {
            conn = getConnection();

            String fileName = filePath.substring(filePath.lastIndexOf("/") + 1, filePath.length() - 4);
            String prjPath = filePath.substring(0, filePath.length() - 4) + ".prj";
            String strWKTMercator = VectorDataUtil.readPRJ(prjPath);
            if (strWKTMercator != "") ProjectTransUtil.setStrWKTMercator(strWKTMercator);
            System.out.println(new Date()+"-------------------1.开始readSHPFile");
            SimpleFeatureCollection colls = VectorDataUtil.readShp(filePath, codeType);
            System.out.println(new Date()+"-------------------2.结束readSHPFile");
            String tempsql = "insert into qh_risk_level_map_polygon (uuid, province, city, area, affiliated_file_name, coordinate, level, " +
                    "extend1, extend2, extend3, extend4, extend5, extend6, extend7, extend8, extend9, extend10, " +
                    "object_code, is_valid, partition_flag, create_user, create_time ) values ";
            StringBuffer sql = new StringBuffer("");
            int total = dataLength;
            int num = 5000;
            boolean bool = false;//是否保存
            //开始时间
            Long begin = new Date().getTime();
            //zyt-test
            int size = 0;
            boolean tempBool = true;//判断是否需要转换经纬度值
            String lonLatStr = "", wkt = "";
            SimpleFeatureIterator iters = colls.features();
            while (iters.hasNext()) {
                count++;
                bool = false;//是否保存
                if(count<total){
                    if(count % num == 0){
                        bool = true;//是否保存
                        //开始时间
                        begin = new Date().getTime();
                    }
                }else{
                    bool = true;//是否保存
                }
                SimpleFeature sf = iters.next();
                List<Object> values = sf.getAttributes();
                if(tempBool){
                    wkt = values.get(0).toString();
                    if (wkt.indexOf("MULTIPOINT") != -1) {
                        lonLatStr = VectorDataUtil.reverseWktMultiPoint(ProjectTransUtil.wkt2lonLat(wkt));
                    } else if (wkt.indexOf("POINT") != -1) {
                        lonLatStr = VectorDataUtil.reverseWktPoint(ProjectTransUtil.wkt2lonLat(wkt));
                    } else if (wkt.indexOf("MULTIPOLYGON") != -1) {
                        lonLatStr = VectorDataUtil.reverseWktMultiPolygon(ProjectTransUtil.wkt2lonLat(wkt));
                    } else if (wkt.indexOf("POLYGON") != -1) {
                        lonLatStr = VectorDataUtil.reverseWktPolygon(ProjectTransUtil.wkt2lonLat(wkt));
                    }
                    values.set(0, lonLatStr);
                    //判断是否经纬度值是否相等
                    if(wkt.equals(lonLatStr)){
                        tempBool = false;//判断是否需要转换经纬度值
                    }
                }
                size = values.size();

                String id = UUIDGenerator.getUUID();
                String affiliatedFileName = fileName;
                String coordinate = values.get(0).toString();
                String level = values.get(1).toString();
                String extend1 = "";
                String extend2 = "";
                String extend3 = "";
                String extend4 = "";
                String extend5 = "";
                String extend6 = "";
                String extend7 = "";
                String extend8 = "";
                String extend9 = "CGCS2000";
                String extend10 = "";

                if (size >= 3) extend1 = (values.get(2).toString() != null ? values.get(2).toString() : "");
                if (size >= 4) extend2 = (values.get(3).toString() != null ? values.get(3).toString() : "");
                if (size >= 5) extend3 = (values.get(4).toString() != null ? values.get(4).toString() : "");
                if (size >= 6) extend4 = (values.get(5).toString() != null ? values.get(5).toString() : "");
                if (size >= 7) extend5 = (values.get(6).toString() != null ? values.get(6).toString() : "");
                if (size >= 8) extend6 = (values.get(7).toString() != null ? values.get(7).toString() : "");
                if (size >= 9) extend7 = (values.get(8).toString() != null ? values.get(8).toString() : "");
                if (size >= 10) extend8 = (values.get(9).toString() != null ? values.get(9).toString() : "");
                //if (size >= 11) extend9 = (values.get(10).toString() != null ? values.get(10).toString() : "");
                if (size >= 12) extend10 = (values.get(11).toString() != null ? values.get(11).toString() : "");

                String objectCode = "";
                String is_valid = "1";
                String partitionFlag = "";
                //String createUser = PlatformSessionUtils.getUserId();
                createUser = (createUser != null ? createUser : "");
                Timestamp createTime = PlatformDateUtils.getCurrentTimestamp();

                sql.append("('" + id + "','" + province + "','" + city + "','" + area + "','" + affiliatedFileName + "','" + coordinate + "','" + level +
                        "','" + extend1 + "','" + extend2 + "','" + extend3 + "','" + extend4 + "','" + extend5 + "','" + extend6 + "','" + extend7 + "','" + extend8 + "','" + extend9 + "','" + extend10
                        + "','" + objectCode + "','" + is_valid + "','" + partitionFlag + "','" + createUser + "','" + createTime + "'),");

                if(bool){
                    String sql_str = tempsql + sql.toString();
                    sql_str = sql_str.substring(0, sql_str.length() - 1);
                    PreparedStatement ps = conn.prepareStatement(sql_str);
                    ps.executeUpdate(sql_str);
                    if (ps != null) ps.close();
                    //结束时间
                    Long end = new Date().getTime();
                    //耗时
                    System.out.println(new Date()+"----count: " + count+",cast : " + (end - begin) / 1000 + " s");
                    //清空
                    sql.setLength(0);
                }
            }
            iters.close();

        } catch (Exception e) {
            e.printStackTrace();
            System.err.println("保存第" + count + "条数据出现异常!");
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (Exception e2) {
                e2.printStackTrace();
            }
        }

        return count;
    }

    //删除--zyt
    /**
     * sql 批量逻辑删除
     *
     * @return
     */
    public int newSqlBatchLogicDeleteEntities(List<String> list) {
        int count = 0;
        Connection conn = null;
        try {
            /**
             * 获取数据库连接
             */
            conn = getConnection();

            List<String> subList = null;
            int total = list.size(), num = 5000, time = total / num + 1, from = 0, to = 0;
            for (int i = 0; i < time; i++) {
                //开始时间
                Long begin = new Date().getTime();
                from = i * num;
                to = (i == time - 1) ? total : (i + 1) * num;
                subList = list.subList(from, to);
                if (subList != null && subList.size() > 0) {
                    StringBuffer sql = new StringBuffer("UPDATE qh_risk_level_map_polygon set is_valid = '0',update_user='" + PlatformSessionUtils.getUserId() + "',update_time='" + PlatformDateUtils.getCurrentTimestamp() + "' WHERE uuid IN ( ");

                    // 上万条数据
                    for (String uuid : subList) {
                        count++;
                        String id = uuid;
                        sql.append("'" + id + "',");
                    }
                    String sql_str = sql.toString();
                    sql_str = sql_str.substring(0, sql_str.length() - 1) + " )";
                    PreparedStatement ps = conn.prepareStatement(sql_str);
                    ps.executeUpdate(sql_str);
                    if (ps != null) ps.close();
                    //结束时间
                    Long end = new Date().getTime();
                    //耗时
                    System.out.println(new Date()+"----count: " + count+",cast : " + (end - begin) / 1000 + " s");
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            System.err.println("删除第" + count + "条数据出现异常!");
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (Exception e2) {
                e2.printStackTrace();
            }
        }
        return count;
    }

    //zyt--查询数据的UUID列表
    public List<String> listQhRiskLevelMapPolygonsUuids(QhRiskLevelMapPolygonQueryParams queryParams, String superCode, String provinceCode, String cityCode, String areaCode) {

        List<String> result = new ArrayList<>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = getConnection();

            StringBuilder sql = new StringBuilder("select uuid from qh_risk_level_map_polygon ");
            StringBuilder whereSql = new StringBuilder(" where 1=1 ");
            if (!PlatformObjectUtils.isEmpty(queryParams.getProvince())) {
                whereSql.append(" and province like '%" + queryParams.getProvince() + "%'");
            }
            /*if (!PlatformObjectUtils.isEmpty(queryParams.getCity())) {
                whereSql.append(" and city like '%" + queryParams.getCity() + "%'");
            }
            if (!PlatformObjectUtils.isEmpty(queryParams.getArea())) {
                whereSql.append(" and area like '%" + queryParams.getArea() + "%'");
            } else {
                whereSql.append(" and (area IS NULL OR LENGTH(area)=0) ");
            }*/
            //修改区县一级无法查询bug--添加查询文件名
            if(!PlatformObjectUtils.isEmpty(queryParams.getAffiliatedFileName())) {
                whereSql.append(" and affiliated_file_name ='" + queryParams.getAffiliatedFileName() + "'");
            }
            whereSql.append(" and is_valid = '1' ");

            String userId = PlatformSessionUtils.getUserId(), isHaveAuthorityControl = queryParams.getIsHaveAuthorityControl();

            List<RoleEntity> roles = PlatformAPI.getOrgAPI().getUserAPI().getRoles(userId);
            // 是否为超级管理员
            boolean containtRole = ServerUtil.isContaintRole(roles, superCode);
            // 是否为省级管理员
            boolean containtRolePro = ServerUtil.isContaintRole(roles, provinceCode);
            // 是否为市级管理员
            boolean containtRoleCity = ServerUtil.isContaintRole(roles, cityCode);
            // 是否为区级管理员
            boolean containtRoleArea = ServerUtil.isContaintRole(roles, areaCode);

            if ("1".equals(isHaveAuthorityControl) && !containtRole) {
                // 如果都不是只能查看个人表单
                if (!containtRolePro && !containtRoleCity && !containtRoleArea) {
                    whereSql.append(" and create_user ='" + userId + "'");
                }
                String divisionName = ServerUtil.getDivisionEntity().getDivisionName();
                // 省级管理员
                if (containtRolePro) {
                    whereSql.append(" and province like '%" + divisionName + "%'");
                }
                // 市级管理员
                if (containtRoleCity) {
                    whereSql.append(" and city like '%" + divisionName + "%'");
                }
                // 区级管理员
                if (containtRoleArea) {
                    whereSql.append(" and area like '%" + divisionName + "%'");
                }
            }

            String sql_str = sql.append(whereSql.toString()).toString();
            ps = conn.prepareStatement(sql_str);
            rs = ps.executeQuery();
            while (rs.next()) {
                String uuid = rs.getString("uuid");
                result.add(uuid);
            }
        } catch (Exception e2) {
            e2.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (Exception e2) {
                e2.printStackTrace();
            }
            try {
                if (ps != null) {
                    // 关闭连接
                    ps.close();
                }
            } catch (Exception e2) {
                e2.printStackTrace();
            }
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (Exception e2) {
                e2.printStackTrace();
            }
        }
        return result;
    }

    //保存GDB文件
    public int newSqlBatchSaveEntities_GDB(String filePath, String province, String city, String area, String codeType, int dataLength, String createUser) {
        int count = 0;
        Connection conn = null;
        try {
            conn = getConnection();

            //String fileName = filePath.substring(filePath.lastIndexOf("/") + 1, filePath.length() - 4);
            String fileName = filePath.substring(filePath.lastIndexOf("/") + 1, filePath.length());
            System.out.println(new Date()+"-------------------1.开始readGDBFile");
            String tempsql = "insert into qh_risk_level_map_polygon (uuid, province, city, area, affiliated_file_name, coordinate, level, " +
                    "extend1, extend2, extend3, extend4, extend5, extend6, extend7, extend8, extend9, extend10, " +
                    "object_code, is_valid, partition_flag, create_user, create_time ) values ";
            StringBuffer sql = new StringBuffer("");
            int total = dataLength;
            int num = 5000;
            boolean bool = false;//是否保存
            //开始时间
            Long begin = new Date().getTime();
            //zyt-test
            boolean tempBool = true;//判断是否需要转换经纬度值
            String lonLatStr = "", wkt = "";

            // 指定文件的名字和路径
            String gdbFile = filePath;
            // 注册所有的驱动
            ogr.RegisterAll();
            // 为了支持中文路径，请添加下面这句代码
            gdal.SetConfigOption("GDAL_FILENAME_IS_UTF8", "YES");
            // 为了使属性表字段支持中文，请添加下面这句
            gdal.SetConfigOption("SHAPE_ENCODING", "CP936");

            // 读取数据，这里以gdb文件为例
            String strDriverName = "OpenFileGDB";
            // 创建一个文件，根据strDriverName扩展名自动判断驱动类型
            Driver oDriver = ogr.GetDriverByName(strDriverName);

            if (oDriver == null) {
                System.out.println(strDriverName + " 驱动不可用！\n");
                return 0;
            }
            DataSource dataSource = oDriver.Open(gdbFile, 0);

            //获取第一个图层
            Layer layer = dataSource.GetLayer(0);
            String layerName = layer.GetName();
            System.out.println("图层名称：" + layerName);
            SpatialReference spatialReference = layer.GetSpatialRef();
            //System.out.println(spatialReference);

            System.out.println("空间参考坐标系：" + spatialReference.GetAttrValue("AUTHORITY", 0)
                    + spatialReference.GetAttrValue("AUTHORITY", 1));

            double[] layerExtent = layer.GetExtent();

            System.out.println("图层范围：minx:" + layerExtent[0] + ",maxx:" + layerExtent[1] + ",miny:" + layerExtent[2]
                    + ",maxy:" + layerExtent[3]);

            FeatureDefn featureDefn = layer.GetLayerDefn();

            int fieldCount = featureDefn.GetFieldCount();

            List<String> fieldNameList = new ArrayList<String>();
            for (int i = 0; i < fieldCount; i++) {
                FieldDefn fieldDefn = featureDefn.GetFieldDefn(i);
                // 得到属性字段类型
                int fieldType = fieldDefn.GetFieldType();
                String fieldTypeName = fieldDefn.GetFieldTypeName(fieldType);
                // 得到属性字段名称
                String fieldName = fieldDefn.GetName();
                fieldNameList.add(fieldName);
            }

            System.out.println(fieldNameList);
            long featureCount = layer.GetFeatureCount();
            System.out.println("图层要素个数：" + featureCount);

            Feature feature2 = null;
            while((feature2 = layer.GetNextFeature()) != null) {
                List<Object> values = new ArrayList<Object>();
                //获取空间信息
                org.gdal.ogr.Geometry geometry = feature2.GetGeometryRef();
                wkt = geometry.ExportToWkt();
                //System.out.println(wkt);
                values.add(wkt);//空间信息
                //System.out.println(feature2.GetGeometryRef().ExportToJson());
                for(int i=0;i<fieldNameList.size();i++){
                    String fieldName = fieldNameList.get(i);
                    //根据属性字段名称获取属性值
                    //System.out.println(fieldName+"\t"+feature2.GetFieldAsString(fieldName));
                    values.add(feature2.GetFieldAsString(fieldName));
                }

                count++;
                bool = false;//是否保存
                if(count<total){
                    if(count % num == 0){
                        bool = true;//是否保存
                        //开始时间
                        begin = new Date().getTime();
                    }
                }else{
                    bool = true;//是否保存
                }
                /*if(tempBool){
                    wkt = values.get(0).toString();
                    if (wkt.indexOf("MULTIPOINT") != -1) {
                        lonLatStr = VectorDataUtil.reverseWktMultiPoint(ProjectTransUtil.wkt2lonLat(wkt));
                    } else if (wkt.indexOf("POINT") != -1) {
                        lonLatStr = VectorDataUtil.reverseWktPoint(ProjectTransUtil.wkt2lonLat(wkt));
                    } else if (wkt.indexOf("MULTIPOLYGON") != -1) {
                        lonLatStr = VectorDataUtil.reverseWktMultiPolygon(ProjectTransUtil.wkt2lonLat(wkt));
                    } else if (wkt.indexOf("POLYGON") != -1) {
                        lonLatStr = VectorDataUtil.reverseWktPolygon(ProjectTransUtil.wkt2lonLat(wkt));
                    }
                    values.set(0, lonLatStr);
                    //判断是否经纬度值是否相等
                    if(wkt.equals(lonLatStr)){
                        tempBool = false;//判断是否需要转换经纬度值
                    }
                }*/

                int size = values.size();

                String id = UUIDGenerator.getUUID();
                String affiliatedFileName = fileName;
                String coordinate = values.get(0).toString();
                String level = values.get(1).toString();
                String extend1 = "";
                String extend2 = "";
                String extend3 = "";
                String extend4 = "";
                String extend5 = "";
                String extend6 = "";
                String extend7 = "";
                String extend8 = "";
                String extend9 = "CGCS2000";
                String extend10 = "";

                if (size >= 3) extend1 = (values.get(2).toString() != null ? values.get(2).toString() : "");
                if (size >= 4) extend2 = (values.get(3).toString() != null ? values.get(3).toString() : "");
                if (size >= 5) extend3 = (values.get(4).toString() != null ? values.get(4).toString() : "");
                if (size >= 6) extend4 = (values.get(5).toString() != null ? values.get(5).toString() : "");
                if (size >= 7) extend5 = (values.get(6).toString() != null ? values.get(6).toString() : "");
                if (size >= 8) extend6 = (values.get(7).toString() != null ? values.get(7).toString() : "");
                if (size >= 9) extend7 = (values.get(8).toString() != null ? values.get(8).toString() : "");
                if (size >= 10) extend8 = (values.get(9).toString() != null ? values.get(9).toString() : "");
                //if (size >= 11) extend9 = (values.get(10).toString() != null ? values.get(10).toString() : "");
                if (size >= 12) extend10 = (values.get(11).toString() != null ? values.get(11).toString() : "");

                String objectCode = "";
                String is_valid = "1";
                String partitionFlag = "";
                //String createUser = PlatformSessionUtils.getUserId();
                createUser = (createUser != null ? createUser : "");
                Timestamp createTime = PlatformDateUtils.getCurrentTimestamp();

                sql.append("('" + id + "','" + province + "','" + city + "','" + area + "','" + affiliatedFileName + "','" + coordinate + "','" + level +
                        "','" + extend1 + "','" + extend2 + "','" + extend3 + "','" + extend4 + "','" + extend5 + "','" + extend6 + "','" + extend7 + "','" + extend8 + "','" + extend9 + "','" + extend10
                        + "','" + objectCode + "','" + is_valid + "','" + partitionFlag + "','" + createUser + "','" + createTime + "'),");

                if(bool){
                    String sql_str = tempsql + sql.toString();
                    sql_str = sql_str.substring(0, sql_str.length() - 1);
                    PreparedStatement ps = conn.prepareStatement(sql_str);
                    ps.executeUpdate(sql_str);
                    if (ps != null) ps.close();
                    //结束时间
                    Long end = new Date().getTime();
                    //耗时
                    System.out.println(new Date()+"----count: " + count+",cast : " + (end - begin) / 1000 + " s");
                    //清空
                    sql.setLength(0);
                }
            }

            //销毁相应的驱动管理对象
            oDriver.delete();
            gdal.GDALDestroyDriverManager();

        } catch (Exception e) {
            e.printStackTrace();
            System.err.println("保存第" + count + "条数据出现异常!");
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (Exception e2) {
                e2.printStackTrace();
            }
        }

        return count;
    }
}
